import { Autowired } from 'j-spring';
import { Service, Transactional } from '../../src';
import { SpringDao } from '../../src';
import { UploadPathCollect } from '../entity/UploadPathCollect';

export type ColumnType = {
  Field: string;
  Type: string;
  Null: string;
  Key: string;
  Default: any;
  Extra: string;
  table: string;
};

@Service()
export class TableDataService {
  @Autowired()
  springDao: SpringDao;

  async showTable(): Promise<string[]> {
    const tableList = await this.springDao.query(`SHOW TABLES;`);
    return tableList.map((v: any) => Object.values(v)[0]) as string[];
  }

  async showColumn(tableName: string): Promise<ColumnType[]> {
    const cols = (await this.springDao.query(
      `SHOW COLUMNS FROM ${tableName}`
    )) as ColumnType[];
    return cols;
  }

  @Transactional()
  async getMaybeExistColumn(): Promise<ColumnType[]> {
    const tableListt = await this.showTable();

    let allCollumn: ColumnType[] = [];

    for (const table of tableListt) {
      const colList = await this.showColumn(table);

      colList.forEach(col => {
        if (!col.Field) return;

        //排除字段名
        if (['id', 'createTime', 'undefined'].indexOf(col.Field) > -1) return;

        //限制字段类型
        if (['longtext', 'varchar(255)'].indexOf(col.Type) === -1) return;

        col.table = table;

        allCollumn.push(col);
      });
    }

    return allCollumn;
  }

  @Transactional()
  async loadAllMaybeFilePath() {
    await this.springDao.query(`truncate UploadPathCollect;`);
    const cols = await this.getMaybeExistColumn();
    for (const col of cols) {
      const datas = await this.springDao.query<{ path: string }>(
        `SELECT ${col.Field} as path FROM ${col.table} WHERE ${col.Field} REGEXP '[0-9]{4}/[0-9]{2}/[0-9]{2}/[0-9]{2}'`
      );
      for (const { path } of datas) {
        const u = new UploadPathCollect();
        u.path = path;
        u.isRemove = 0;
        await this.springDao.save(u);
      }
    }
    const infos = await this.springDao.query<{ count: number }>(
      `SELECT count(*) as count FROM UploadPathCollect WHERE 1=1`
    );
    return infos[0]['count'];
  }
}
